package com.datasecops.scannerservices.extractwatermark;

import com.datasecops.scannerservices.watermark.JNILinuxSo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.Calendar;

/**
 * @author: GeXiaoLong
 * @time: 2023/2/14 11:21
 */
@Service
@Slf4j
public class OracleExtract {
    @Resource
    private ExtractWatermark extractWatermark;

    @Resource
    private JNILinuxSo jniLinuxSo;

    private void OracleToCsv() throws ClassNotFoundException, SQLException, IOException {
        //（1）使用Java JDBC连接数据库：

        Class.forName("oracle.jdbc.driver.OracleDriver");

        String url = "jdbc:oracle:thin:@10.26.18.66:1521:orcl";

        String user = "soul";

        String password = "1234567";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        //（2）构建SQL查询语句，查询出所需要的数据：
        //然后使用JDBC获取表的建表语句：
        String model = "soul".toUpperCase();
        String tableName = "users";
        String sql = "SELECT * FROM " + "\"" + tableName + "\"";
        System.out.println(sql);
        //这个是表数据
        Statement stmt = conn.createStatement();


        //获取表结构的相关信息
        DatabaseMetaData metaData = conn.getMetaData();
        //这里是获取表结构参数
        ResultSet pkInfo = metaData.getPrimaryKeys(null, model, tableName);
        System.out.println(pkInfo == null);
        StringBuilder surface = new StringBuilder("");
        String primary = null;
        while (pkInfo.next()) {
            System.out.print("数据库名称:" + pkInfo.getString("TABLE_CAT") + "                  ");
            System.out.print("表名称:" + pkInfo.getString("TABLE_NAME") + "                  ");


            surface.append("CREATE TABLE");
            surface.append(" ");
            surface.append(pkInfo.getString("TABLE_NAME"));
            surface.append(" ");
            surface.append("(");
            surface.append("\n");


            System.out.print("主键列的名称:" + pkInfo.getString("COLUMN_NAME") + "                  ");
            primary = pkInfo.getString("COLUMN_NAME");
            System.out.print("类型:" + pkInfo.getString("PK_NAME") + "                  ");
            System.out.println("");
        }
        ResultSet tableInfo = metaData.getColumns(null, model, tableName, null);
        while (tableInfo.next()) {
            // 表的名字
            System.out.print("表名:" + tableInfo.getString("TABLE_NAME") + "    ");
            // 列的名称
            System.out.print("列名:" + tableInfo.getString("COLUMN_NAME") + "    ");

            // 默认值
            System.out.print("默认值 :" + tableInfo.getString("COLUMN_DEF") + "  ");
            // 字段的类型
            System.out.print("字段的类型:" + tableInfo.getString("TYPE_NAME") + "   ");

            // 是否可以为空
            System.out.print("是否可以为空:" + tableInfo.getString("IS_NULLABLE") + "  ");

            // 是否为自增
            System.out.print("是否为自增:" + tableInfo.getString("IS_AUTOINCREMENT") + " ");
            // 字段说明
            System.out.print("字段说明:" + tableInfo.getString("REMARKS") + "  ");
            // 长度(有时候是错的)
            System.out.print("长度:" + tableInfo.getString("COLUMN_SIZE") + " ");
            surface.append(tableInfo.getString("COLUMN_NAME"));
            surface.append(" ");
            surface.append(tableInfo.getString("TYPE_NAME"));
            surface.append(" ");
            surface.append("(");
            if ("DATE".equalsIgnoreCase(tableInfo.getString("TYPE_NAME"))) {
                surface.append(6);
            } else {
                surface.append(tableInfo.getString("COLUMN_SIZE"));
            }
            //surface.append(tableInfo.getString("COLUMN_SIZE"));
            surface.append(")");
            surface.append(" ");
            surface.append("COLLATE utf8mb4_unicode_ci");
            surface.append(" ");
            if ("NO".equalsIgnoreCase(tableInfo.getString("IS_NULLABLE"))) {
                surface.append("NOT NULL");
            } else {
                surface.append("DEFAULT NULL");
            }
            surface.append(" ");
            surface.append(",");
            surface.append("\n");
            System.out.println();
        }
        surface.append("PRIMARY KEY");
        surface.append(" ");
        surface.append("(");
        surface.append(primary);
        surface.append(")");
        surface.append(" ");
        surface.append("USING BTREE");
        surface.append("\n");
        surface.append(")");
        surface.append(" ");
        surface.append("ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
        String s = surface.toString();
        String replace = null;
        replace = s.replace("NVARCHAR2", "varchar");
        replace = replace.replace("NUMBER", "int");
        replace = replace.replace("DATE", "datetime");
        replace = replace.replace("RAW", "blob");
        replace = replace.replace("CLOB", "text");

        System.out.println(replace);

        //（3）将查询结果写入CSV文件：
        ResultSet rs = stmt.executeQuery(sql);

/*        OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream("P:\\text\\your_files.csv"),
                StandardCharsets.UTF_8);//win版的*/

        OutputStreamWriter fw = new OutputStreamWriter(new FileOutputStream("/root/your_file.csv"),
                StandardCharsets.UTF_8);
        ResultSetMetaData metaData1 = rs.getMetaData();
        int columeCount = metaData1.getColumnCount();//返回列数
        String String = "";

        for (int i = 1; i <= columeCount; i++) {
            if (!(i == columeCount)) {
                String = String + metaData1.getColumnName(i) + ",";
            } else {
                String = String + metaData1.getColumnName(i) + "\n";
            }
        }
        fw.append(String);

        while (rs.next()) {
            String outputString = "";
            for (int i = 1; i <= columeCount; i++) {
                if (!(i == columeCount)) {
                    String str = rs.getString(i);
                    boolean b = rs.wasNull();
                    int i1 = -1;
                    if (str != null) {
                        i1 = str.indexOf("\n");//如果获取到的字符串中含有换位符\n ,那么i1的值就不是-1,只有不包含的时候才是-1;
                    }
                    if (i1 != -1) {
                        String replaces = null;
                        if (str != null) {
                            replaces = str.replace("\n", "");//把自动生成的换位符去掉
                        }
                        outputString = outputString + replaces + ",";
                    } else {
                        if (b) {
                            outputString = outputString + "NULL" + ",";
                        } else {
                            outputString = outputString + rs.getString(i) + ",";
                        }
                        //outputString = outputString + rs.getString(i) + ",";
                    }
                } else {
                    outputString = outputString + rs.getString(i) + "\n";
                }
            }
            //           String outputString = rs.getString(1) + "," + rs.getString(2) + "," + rs.getString(3) + "\n";

            fw.append(outputString);

        }
        fw.flush();

        fw.close();

        //（4）关闭数据库连接：

        rs.close();

        stmt.close();

        conn.close();

        importData(replace, tableName);

    }
     /**
      *创建新表和导入csv
      * @Description
      * @author GeXiaoLong
      * @time 2023/2/14 15:47
      * @param
      * @return
      */
    public void importData(String replace, String tableName) throws SQLException {


        log.error("数据库安装程序进来了吗?");
        Statement st = null;
        try {
            log.error("数据库安装程序运行进来了吗1?");
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
            String username = "root";
            String password = "Wzx.1104";
            Connection c = DriverManager.getConnection(url, username, password);
            st = c.createStatement();
            st.executeUpdate("CREATE DATABASE IF NOT EXISTS WatermarkExtraction");//创建数据库
            st.executeUpdate("USE WatermarkExtraction");
         /*   ResultSet resultSet = st.executeQuery("SHOW TABLES LIKE 'SOULWJ'");
            while (resultSet.next()){
                String string = resultSet.getString(1);
                System.out.println(string);
            }*/
            int i = st.executeUpdate(replace);//创建一张新表
            System.out.println(i);

            String loadDataQuery = " LOAD DATA LOCAL INFILE '/root/your_file.csv' INTO TABLE " +
                    tableName + " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES";//linux

/*
                    String loadDataQuery = " LOAD DATA LOCAL INFILE 'E://DB//your_files.csv' INTO TABLE " +
                tableName +" FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' IGNORE 1 LINES";
*/

            //rs = st.executeQuery("select * from student");
            System.out.println(loadDataQuery);

            //执行导入csv文件代码
            boolean execute = st.execute(loadDataQuery);
            int updateCount = st.getUpdateCount();
            while (!(updateCount > 0)){
                System.out.println("正在导入");
            }
            System.out.println(execute);//导入csv文件
//System.out.println(loadDataQuery);
            //st.executeUpdate( loadDataQuery);
            //st.execute(loadDataQuery);
        } catch (Exception e) {
            log.error("数据库添加程序运行失败,原因是: ",e);
        } finally {
            st.close();
        }

    }
    /**
     *获取csv文件的行数
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/15 9:53
     * @param
     * @return
     */
    public Integer ReadCSV() {
        int count = 0;
        try {
            FileReader fr = new FileReader("/root/your_file.csv");
            BufferedReader br = new BufferedReader(fr);
            //count = 0;
            while (br.readLine() != null) {
                count++;
            }
            System.out.println("行数：" + count);
            br.close();
            fr.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return count;
    }

    /**
     *数据库导入的行数
     * @Description
     * @author GeXiaoLong
     * @time 2023/2/15 9:54
     * @param
     * @return
     */
    public Integer DBRows(String name) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://10.26.18.58:3306/mysql?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai";
        String username = "root";
        String password = "Wzx.1104";
        String tableName = name;
        String mode = "WatermarkExtraction";


        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Statement stmt = conn.createStatement();

        String sql = "SELECT COUNT(*) FROM " +mode +"."+ tableName;



        ResultSet rs = stmt.executeQuery(sql);
        int count = 0;
        if (rs.next()) {
            count = rs.getInt(1);
        }
        return count;
    }

     /**
      *Oracle数据库水印的提取
      * @Description
      * @author GeXiaoLong
      * @time 2023/2/15 14:32
      * @param
      * @return
      */
    public Integer oracleWatermarkInformationExtraction(String tableName) throws SQLException, IOException,
            ClassNotFoundException {
        Calendar calendar = Calendar.getInstance();
        extractWatermark.text(tableName);
        //mysqlExtract.watermarkingDatabase();
        long time = calendar.getTimeInMillis();
        OracleToCsv();
        Integer integer = ReadCSV();//csv文件获取到的行数
        Integer integer1 = DBRows(tableName);//数据库导入的行数
        Calendar calendars = Calendar.getInstance();
        long times = calendars.getTimeInMillis();
        log.error("导出和导入一共花费了"+(times-time)/1000 + "秒");
        Integer text = null;
        while ((integer - 1) == integer1){
            text = jniLinuxSo.text();
            break;
        }

        return text;
    }

    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        OracleExtract oracleExtract = new OracleExtract();
        oracleExtract.OracleToCsv();
    }

}